package com.jiefeng.manager.common.util;

import com.alibaba.fastjson.JSONObject;
import com.jiefeng.manager.common.exception.ExcelImportExportException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * Excel批量导入工具类
 *
 *  * Created by WuZhiqi on 2016/5/26.
 *
 */
public class ExcelBatchImport{

    private static Logger logger = LoggerFactory.getLogger(ExcelBatchExport.class);

    public static <T> List<T> excelBatchImport(File file, Class<T> clazz) throws ExcelImportExportException {
        try{
            List<T> resultList = new ArrayList<T>();
            List<Map<String, String>> readMetadata = readExcel(file);
            Map<String, Object> objectMapper = new HashMap<String, Object>();
            for(Map<String, String> item : readMetadata){
                //把中文表头转化为POJO的属性名
                objectMapper.clear();
                Field[] fields = clazz.getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    ExcelTemplate excelTemplateAnnotation =  fields[i].getAnnotation(ExcelTemplate.class);
                    if(null != excelTemplateAnnotation){
                        String columnValue = item.get(excelTemplateAnnotation.columnName());
                        objectMapper.put(fields[i].getName(), columnValue);
                    }
                }
                JSONObject jsonObject = new JSONObject(objectMapper);
                String jsonString = jsonObject.toJSONString();
                T object = JSONObject.parseObject(jsonString, clazz);
                resultList.add(object);
            }
            return resultList;
        }catch (Exception exception){
            throw new ExcelImportExportException(exception);
        }
    }

    /**
     * 读取Excel
     *
     * @return
     *
     */
    protected static List<Map<String, String>> readExcel(File file) throws ExcelImportExportException {
        if (file.getName().toLowerCase().endsWith("xls")) {
            return read2003(file);
        } else if (file.getName().toLowerCase().endsWith("xlsx")) {//2007
            return read2007(file);
        } else {
            throw new ExcelImportExportException("Excel 文件格式问题，不能解析");
        }
    }

    /**
     * 读取读取97-2003格式Excel
     *
     * @param file  文件
     *
     * @throws Exception
     */
    private static List<Map<String, String>> read2003(File file) throws ExcelImportExportException {
        try {
            //返回结果集
            List<Map<String, String>> valueList=new ArrayList<Map<String, String>>();

            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
            //获取第一个sheet
            HSSFSheet sheet = workbook.getSheetAt(0);
            //第一个sheet 的所有的row行数
            int sumRow = sheet.getPhysicalNumberOfRows();
            Map<Integer,String> keys = new HashMap<Integer, String>();
            int cells=0;
            // 遍历行­（第1行  表头） 准备Map里的key
            HSSFRow firstRow = sheet.getRow(0);
            if (firstRow != null) {
                // 获取到Excel文件中的所有的列
                cells = firstRow.getPhysicalNumberOfCells();
                // 遍历列
                for (int j = 0; j < cells; j++) {
                    // 获取到列的值­
                    HSSFCell cell = firstRow.getCell(j);
                    String cellValue = ExcelUtil.getStringCell(cell);
                    keys.put(j,cellValue);
                }
            }
            // 遍历行­（从第二行开始）
            for (int i = 1; i < sumRow; i++) {
                // 读取左上端单元格(从第二行开始)
                HSSFRow row = sheet.getRow(i);
                // 行不为空
                if (row != null) {
                    //准备当前行 所储存值的map
                    Map<String, String> val = new HashMap<String, String>();
                    for (int j = 0; j < cells; j++) {
                        HSSFCell cell = row.getCell(j);
                        if(null != cell){
                            String cellValue = ExcelUtil.getStringCell(cell);
                            if(StringUtils.isNotBlank(cellValue)){
                                val.put(keys.get(j),cellValue);
                            }
                        }
                    }
                    //第I行所有的列数据读取完毕，放入valuelist
                    if(val.size() > 0){
                        valueList.add(val);
                    }
                }
            }
            logger.info("读取Excel成功,excelName" + file.getName());
            return valueList;
        }catch (IOException exception){
            throw new ExcelImportExportException("读取上传文件失败", exception);
        }
    }

    /**
     * 读取读取2007-2013格式Excel
     * @param file
     * @return
     * @throws Exception
     */
    private static List<Map<String, String>> read2007(File file) throws ExcelImportExportException {
        try {
            //返回结果集
            List<Map<String, String>> valueList=new ArrayList<Map<String, String>>();

            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
            //获取第一个sheet
            XSSFSheet sheet = workbook.getSheetAt(0);
            //第一个sheet 的所有的row行数
            int sumRow = sheet.getPhysicalNumberOfRows();
            // 定义 row、cell
            XSSFRow row;
            // 循环输出表格中的第一行内容   表头
            Map<Integer, String> keys=new HashMap<Integer, String>();
            row = sheet.getRow(0);
            if(row !=null){
                for (int j = row.getFirstCellNum(); j <=row.getPhysicalNumberOfCells(); j++) {
                    if(row.getCell(j)!=null){
                        if(!row.getCell(j).toString().isEmpty()){
                            keys.put(j, row.getCell(j).toString());
                        }
                    }
                }
            }
            // 循环输出表格中的从第二行开始内容
            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    Map<String, String> val = new HashMap<String, String>();
                    for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
                        XSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            String cellValue = ExcelUtil.getStringCell(cell);
                            if(StringUtils.isNotBlank(cellValue)){
                                val.put(keys.get(j), cellValue);
                            }
                        }
                    }
                    //第I行所有的列数据读取完毕，放入valuelist
                    if(val.size() > 0){
                        valueList.add(val);
                    }
                }
            }
            logger.info("读取Excel成功,excelName" + file.getName());
            return valueList;
        }catch (IOException exception){
            throw new ExcelImportExportException("读取上传文件失败", exception);
        }
    }
}
